IF OBJECT_ID('dbo.RptCSCRExecutedAgreementsDetails') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptCSCRExecutedAgreementsDetails
    IF OBJECT_ID('dbo.RptCSCRExecutedAgreementsDetails') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptCSCRExecutedAgreementsDetails >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptCSCRExecutedAgreementsDetails >>>'
END
go

  
-- =======================================================================      
-- Procedure Name: dbo.RptCSCRExecutedAgreementsDetails      
--      
-- Author:      Kevin Cashman, Parallax Consulting LLC      
--      
-- Create date: 7/19/2007      
--      
-- Description: Source of Report, "RptCSCRExecutedAgreementsDetails"      
--                  In Insight DB Reporting Services      
--      
-- Lists all corporate-sponsored clinical research agreements executed       
-- within a specified time period (i.e., 3 months, YTD, annual); distributed       
-- quarterly to MGH/BWH/PHS VPs and department chiefs and ad hoc upon request       
-- by senior leadership.      
--      
-- Parameters:        
--             @StartDate          Date of Status "CSR Executed"      
--             @EndDate                  
--             @CSCRCaseManager               
--             @Institution           
--             @Department      
--             @Unit           
--      
--      
-- Returns:     INT, 0=Success, -1=Error      
--      
-- SampleCall:      
-- EXECUTE dbo.RptCSCRExecutedAgreementsDetails()      
--      
--------------------------------------------------------------------------      
-- Date        Initials  Modification      
--------------------------------------------------------------------------      
-- 7/19/07     KC        Created      
-- 8/8/2007    KC        Added Real Fields,Fixed Naming Conventions,       
--                       DomainUserId      
-- 9/11/07     KC        Changed to new Source tables      
-- 9/17/07     KC        Security was using old function. Fixed.      
-- 9/20/07     KC        Changed Sponsortype to SubActivityType      
-- 9/21/07     KC        Changed Params to INTs (Inst,Dept,Unit)      
--                       Added IRBApprovalDateFrom      
-- 9/24/07     KC        Changed Search Date Logic. Migrated data was      
--                       fouling things up...      
-- 9/25/07     KC        Added reference to dbo.RexStatusHistory      
--                       Added logic to get first time going to CSCR Executed      
-- 9/26/07     KC        Changed Join to "First View Proposals" to LEFT OUTER      
--                       because historical data would not be returned.      
-- 9/27/07     KC        Got PropTitle from AInfo table instead.      
-- 10/1/07     KC        Fixed outdated function reference. fnGetMultiCriteria      
-- 10/11/07    KC        Added JOIN for PropTransactions, Amendments      
-- 10/17/07    KC        Added Param for IncludeDanaFarber      
-- 10/18/07    KC        Fixed Searching on Dates with TimeComponent      
-- 11/1/07     KC        Added Ignore for InstrumentType = 'Grant'      
-- 11/5/07     KC        Add date search to Amendments      
-- 11/15/07    CB        Added PARTNERS_PD_PROP_STAT_24 to status search       
-- 11/20/07    KC        User-Requested Fixes: Ignore PropStatDate =       
--                       ConversionDates, Strip off (C) and (S) from      
--                       SubActivity, Ignore CSCRCaseMgr not assigned.      
-- 11/27/07    KC        Added Dates to resultset for troubleshooting.      
--                       Changed display of FullyExecutedAwardedDate a bit...      
-- 01/09/08    KC        New view for ProjectBudget data, dbo.vwAgreeementClinicalTrialBudget      
--                       Also, was able to remove UDF join because view provides      
--                       the fields.      
-- 01/11/08    KC        Extra "e" in new view name removed.      
-- 02/27/08    KC        Removed Security per Partners Request      
-- 03/28/08    KC        Added SubContract Info to Result set.      
-- 04/09/08    KC        @IncludeDanaFarber now refers to Institution, not Sponsor      
--                       Added 'PARTNERS_PD_PROP_STAT_2' (Fully Executed/Awarded - Activated)      
--                       to Status Check on ProposalView      
-- 09/02/08    KC        Request for Enhancement to add search on Subcontract Deliverables      
-- 09/09/09    PC        InstitutionId, DepartmentId, UnitId and RptCSCRExecutedAgreements is renamed to RptCSCRExecutedAgreementsDetails       
--      
--  ** At this point, the last request calls out for a rewrite of the proc      
--  The search for Master and Amendment is actually redundant, since the      
--  second search includes Masters too. However, the report is in Production      
--  and time restrictions deem it NOT prudent to rewrite, but to retrofit instead. KC      
--      
-- =======================================================================

CREATE PROCEDURE [dbo].[RptCSCRExecutedAgreementsDetails]        
     (        
     @StartDate                         DATETIME,          
     @EndDate                           DATETIME,        
     @CSCRCaseManager                   VARCHAR(100) = NULL,        
     @Institution                       VARCHAR(100) = NULL,                     
     @Department                        VARCHAR(100) = NULL,        
     @Unit                              VARCHAR(100) = NULL,        
     @IncludeDanaFarber                 VARCHAR(3) = NULL,        
     @DomainUserId                      INT        
     )         
                                           
AS        
BEGIN        
     -- Initial Settings        
     SET NOCOUNT ON        
             
     -- Declare Local Variables        
     DECLARE   @Error INT,        
               @RowCount INT,        
               @ConversionDate1 DATETIME,        
               @ConversionDate2 DATETIME,  
               @Departmentchk varchar(30),  
               @Unitchk varchar(30)        
        
     -- Default this, Records with these PropStatDate(s) will not be returned        
     SET  @ConversionDate1 = '8/3/2007'        
     SET  @ConversionDate2 = '8/4/2007'        
     SET  @Departmentchk =NULL  
     SET  @Unitchk =NULL  
     -- ALL checking        
  IF @Department IS NULL SET @Departmentchk = 'NULLABLE'  
     IF @Unit IS NULL SET @Unitchk = 'NULLABLE'  
     IF @CSCRCaseManager IN ('ALL','','0') SET @CSCRCaseManager = NULL        
     IF @Institution IN ('ALL','','0') SET @Institution = NULL        
     IF @Department IN ('ALL','','0') SET @Department = NULL        
     IF @Unit IN ('ALL','','0') SET @Unit = NULL  
       
     IF @IncludeDanaFarber IN ('Yes','1') SET @IncludeDanaFarber = NULL ELSE SET @IncludeDanaFarber = '2'        
             
  
  SELECT     VW.prop_no ,  
     VW.PropStatDate ,  
     VW.PropBudStat ,  
     VW.PropBudStatDate ,  
     VW.PropStat ,  
     VW.prop_stat ,  
     VW.TransactionType ,  
     VW.Proj_Title  
   INTO  #tmpIEP  
   FROM       dbo.vwInfoEdProposalsSrLeader AS VW  
   INNER JOIN -- First time going to "CSCR - Executed"        
   ( SELECT     prop_no ,  
       MIN( PropStatDate ) AS FirstPropStatDate  
  FROM       dbo.vwInfoEdProposalsSrLeader  
  WHERE      stat_type = 0  
      AND prop_stat IN(  'PARTNERS_P_PROP_STAT_66' ,  
          'PARTNERS_PD_PROP_STAT_24' ,  
          'PARTNERS_PD_PROP_STAT_2' )  
  GROUP BY   prop_no ) AS FIRST  
   ON         VW.prop_no = FIRST.prop_no  
     AND VW.PropStatDate = FIRST.FirstPropStatDate  
     AND VW.stat_type = 0  
     AND VW.prop_stat IN(  'PARTNERS_P_PROP_STAT_66' ,  
            'PARTNERS_PD_PROP_STAT_24' ,  
            'PARTNERS_PD_PROP_STAT_2' )   
  
 UPDATE #tmpIEP  
 SET PropStatDate = CONVERT(DATETIME,CONVERT(VARCHAR(12),PropStatDate))  
   
  
 SELECT *  
 INTO #tmpRH  
 FROM dbo.RexStatusHistory  
  
 UPDATE #tmpRH  
 SET FEAPDate = CONVERT(DATETIME,CONVERT(VARCHAR(12),FEAPDate))  
  
  SELECT     CASE  
                 WHEN CHARINDEX(  '-' , VW.inst_no ) > 0 THEN SUBSTRING(  VW.inst_no ,1 ,CHARINDEX(  '-' ,VW.inst_no ) - 1 )  
                 ELSE VW.inst_no  
             END AS FolderNumber ,  
             VW.inst_no ,  
             VW.PropStatDate ,  
             VW.PropStat ,  
             VW.TransactionType ,  
             VW.Proj_Title ,  
             VW.PropBudStat ,  
             VW.PropBudStatDate  
 INTO #tmpPT  
  FROM       dbo.vwInfoEdProposalsSrLeader AS VW  
  INNER JOIN -- First time going to "CSCR - Executed"        
  ( SELECT     prop_no ,  
       MIN( PropStatDate ) AS FirstPropStatDate  
  FROM       dbo.vwInfoEdProposalsSrLeader  
  WHERE      stat_type = 0  
      AND prop_stat IN(  'PARTNERS_P_PROP_STAT_66' ,  
          'PARTNERS_PD_PROP_STAT_24' ,  
          'PARTNERS_PD_PROP_STAT_2' )  
      AND ( TransactionType LIKE '%Amendment%'  
       OR TransactionType LIKE '%Master%' )  
  GROUP BY   prop_no ) AS FIRST  
 ON         VW.prop_no = FIRST.prop_no  
    AND VW.PropStatDate = FIRST.FirstPropStatDate  
    AND VW.stat_type = 0  
    AND VW.prop_stat IN(  'PARTNERS_P_PROP_STAT_66' ,  
           'PARTNERS_PD_PROP_STAT_24' ,  
           'PARTNERS_PD_PROP_STAT_2' )    
   
 UPDATE #tmpPT  
 SET PropStatDate = CONVERT(DATETIME,CONVERT(VARCHAR(12),PropStatDate))  
  
  
 SELECT     CSRD.prop_no ,  
    CSRD.ReportId ,  
    CSRD.ReportName ,  
    CSRD.REPORTCAT AS TransactionType ,  
    CSRD.REPORTSTAT ,  
    MIN( dbo.fnRemoveTime ( CSRD.ProcessedDate ) ) AS StatusDate  
 INTO #tmpSUBCON  
 FROM       dbo.vwInfoEdCSCRDeliverables AS CSRD  
 WHERE      CSRD.REPORTCAT_CODE = 'REPORTCAT_34'  
   AND CSRD.REPORTSTAT_CODE = 'REPORTSTAT_13'  
   AND dbo.fnRemoveTime ( CSRD.ProcessedDate ) BETWEEN @StartDate AND @EndDate  
   AND dbo.fnRemoveTime ( CSRD.ProcessedDate ) NOT IN(  @ConversionDate1 ,  
                 @ConversionDate2 )  
 GROUP BY   CSRD.prop_no ,  
    CSRD.ReportId ,  
    CSRD.ReportName ,  
    CSRD.REPORTCAT ,  
    CSRD.REPORTSTAT   
  
 UPDATE #tmpSUBCON  
 SET StatusDate = CONVERT(DATETIME,CONVERT(VARCHAR(12),StatusDate))  
  
       
     -- Result Set        
     SELECT    AI.PrincipalInvestigator AS PrincipalInvestigator,        
               CASE WHEN PT.TransactionType = 'Master' OR PT.TransactionType IS NULL        
                         THEN AI.ProposalTitle        
                         ELSE ISNULL(PT.Proj_Title,IEP.Proj_Title)        
               END AS ProposalTitle,                        
               AI.SponsorName AS PrimarySponsor,        
               CASE WHEN CHARINDEX('(',AI.SubActivityType) > 0        
                    THEN SUBSTRING(AI.SubActivityType,1,CHARINDEX('(',AI.SubActivityType) - 2)        
                    ELSE AI.SubActivityType        
               END AS SubActivityType,        
               AI.IRBApprovalDateFrom AS ApprovalDate,        
               AI.SecondarySponsor AS SecondarySponsor,        
               AI.InstrumentType AS SubAgreementType,                  
               CTB.InPatientCareTotal AS InPatientCareTotal,        
               CTB.OneTimeFees AS OneTimeFees,        
               CASE WHEN PT.TransactionType = 'Master' OR PT.TransactionType IS NULL        
                    THEN CTB.ExpectedPatientEnrollment         
                    ELSE NULL        
               END AS ExpectedPatientEnrollment,        
               CASE WHEN PT.TransactionType = 'Master' OR PT.TransactionType IS NULL        
                    THEN CTB.IndirectCostRate         
                    ELSE NULL        
               END AS FARate,        
               CASE WHEN PT.TransactionType = 'Master' OR PT.TransactionType IS NULL        
                    THEN CTB.ProjectDirectCost         
                    ELSE NULL        
               END AS ProjectDirectCost,        
               ISNULL(PT.PropBudStat,IEP.PropBudStat) AS PropBudStat,        
               ISNULL(PT.PropBudStatDate,IEP.PropBudStatDate) AS PropBudStatDate,        
               ISNULL(PT.PropStat,IEP.PropStat) AS PropStat,        
               ISNULL(PT.PropStatDate,IEP.PropStatDate) AS PropStatDate,        
               -- Was simple ISNULL check, but this displays WHY the Record is returned (datewise)        
               CASE WHEN IEP.PropStatDate BETWEEN @StartDate AND @EndDate THEN IEP.PropStatDate        
                    WHEN PT.PropStatDate BETWEEN @StartDate AND @EndDate THEN PT.PropStatDate        
                    WHEN RH.FEAPDate BETWEEN @StartDate AND @EndDate THEN RH.FEAPDate        
                    ELSE IEP.PropStatDate        
               END AS FullyExecutedAwardedDate,        
               AI.CSCRCaseManager AS CSCRCaseManager,        
               AI.CSCRCaseManagerName AS CSCRCaseManagerName,        
               AI.AgreementType AS AgreementType,        
               CASE WHEN ISNULL(PT.TransactionType,ISNULL(IEP.TransactionType,'Master')) = 'Master'        
                    THEN 'Agreement'         
                    ELSE ISNULL(PT.TransactionType,ISNULL(IEP.TransactionType,'Agreement'))        
               END AS TransactionType,        
               AI.InstrumentType AS ProposalType,        
               AI.Institution AS Institution,        
               AI.Department AS Department,        
               AI.ChiefCode AS ChiefCode,        
               ISNULL(AI.Unit,'(None)') AS UnitCode,        
               AI.ProposalStatus AS ProposalStatus,        
               AI.InfoEdPropNumber AS InfoEdPropNumber,        
               AI.PrincipalInvestigatorId AS PrincipalInvestigatorId,        
               AI.SponsorId AS SponsorId,        
               AI.ResourceId AS AgrResourceId,        
               AI.FolderNumber AS FolderNumber,        
               PT.inst_no AS AmendmentFolderNumber,        
               AI.AgreementId AS AgreementId,        
               PT.PropStatDate AS AmendFirstPropStatChgDate,        
               IEP.PropStatDate AS MstrFirstPropStatChgDate,        
               RH.FEAPDate AS LegFEAPDate,        
               SUBCON.ReportName AS SubContractName,        
               SUBCON.TransactionType AS SubContractTranType,        
               SUBCON.REPORTSTAT AS SubContractStatus,        
               SUBCON.StatusDate AS SubContractStatusDate,      
               AI.InstitutionId,  
               AI.DepartmentId,  
               AI.UnitId  
FROM       dbo.AgrAgreement_Info AS AI  
LEFT OUTER JOIN -- New view provides TotalProjectCosts and other fields we need        
dbo.vwAgreementClinicalTrialBudget AS CTB  
ON         AI.AgreementId = CTB.AgreementId  
LEFT OUTER JOIN -- Searching on FIRST Status Change Date!        
#tmpIEP AS IEP  
ON         AI.InfoEdPropNumber = IEP.prop_no  
LEFT OUTER JOIN -- Get Amendments Master would be returned on the prior subselect        
#tmpPT AS PT  
ON         AI.FolderNumber = PT.FolderNumber  
LEFT OUTER JOIN #tmpRH AS RH  
ON         AI.FolderNumber = RH.FolderNumber  
LEFT OUTER JOIN -- SubContract Deliverable Fields, Get Current Status on SubContract Deliverables        
#tmpSUBCON AS SUBCON  
ON         AI.InfoEdPropNumber = SUBCON.prop_no  
WHERE      ( ( IEP.PropStatDate  BETWEEN @StartDate AND @EndDate  
               AND  IEP.PropStatDate  NOT IN(  @ConversionDate1 ,  
                                                                  @ConversionDate2 ) )  
              OR -- Master Docs        
             (RH.FEAPDate BETWEEN @StartDate AND @EndDate)  
              OR -- Historical        
             (  PT.PropStatDate  BETWEEN @StartDate AND @EndDate  
               AND PT.PropStatDate  NOT IN(  @ConversionDate1 ,  
                                                                 @ConversionDate2 ) )  
              OR -- Amendments        
             ( SUBCON.StatusDate  BETWEEN @StartDate AND @EndDate  
               AND  SUBCON.StatusDate NOT IN(  @ConversionDate1 ,  
                                                                   @ConversionDate2 ) ) )  
       AND -- Subcontract Deliverables        
       ( RTRIM( AI.ActivityDescription ) = 'Clinical Research' )  
       AND ( RTRIM( AI.InstrumentType ) != 'Grant' )  
       AND ( AI.CSCRCaseManager IS NOT NULL  
             AND RTRIM( AI.CSCRCaseManager ) != '' )  
       AND -- Optional Search Criteria        
       ( @Institution IS NULL  
          OR AI.InstitutionId IN( SELECT    Criteria_ID  
                                  FROM       dbo.fnGetMultiCriteria ( @Institution ) ) )  
       AND ( ( @Department IS NULL  
               AND @Departmentchk IS NULL )  
              OR AI.DepartmentId IN( SELECT     Criteria_ID  
                                     FROM       dbo.fnGetMultiCriteria ( @Department ) )  
              OR ( @Departmentchk = 'NULLABLE'  
                   AND AI.DepartmentId IS NULL ) )  
       AND ( ( @Unit IS NULL  
               AND @Unitchk IS NULL )  
              OR AI.UnitId IN( SELECT     Criteria_ID  
                               FROM       dbo.fnGetMultiCriteria ( @Unit ) )  
              OR ( @Unitchk = 'NULLABLE'  
                   AND AI.UnitId IS NULL ) )  
       AND ( @CSCRCaseManager IS NULL  
              OR AI.CSCRManagerId IN( SELECT     Criteria_ID  
                                      FROM       dbo.fnGetMultiCriteria ( @CSCRCaseManager ) ) )  
       AND  
       -- MattWickert and CSCRGroup Requests Option to IGNORE 4 DanaFarber Cancer Institute Sponsors        
       --(@IncludeDanaFarber IS NULL OR AI.SponsorID NOT IN (9268,1026,1546,4715))          
       -- Change to Institution        
       ( @IncludeDanaFarber IS NULL  
          OR AI.InstitutionId NOT IN(  350 ,  
                                       351 ) )  
ORDER BY   AI.Institution ,  
           AI.Department ,  
           AI.PrincipalInvestigator  
    
        
        
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT        
     IF @Error!=0        
          RETURN -1        
                  
     -- Return Success        
     RETURN 0        
        
       
END   
go

IF OBJECT_ID('dbo.RptCSCRExecutedAgreementsDetails') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptCSCRExecutedAgreements >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptCSCRExecutedAgreementsDetails >>>'
go

GRANT EXECUTE ON dbo.RptCSCRExecutedAgreementsDetails TO InsightReport,Insight
go

